# -*- coding: utf-8 -*-
import pymysql
import sys
import random
import time
import logging
import json

# 初始化log
log_path = 'D:/python/pycharm/mitmdump_01/douyin_spider/logs/'
file_name = time.strftime("%Y-%m-%d", time.localtime()) + ".log"
# 初始化logging记录日志设置
logging.basicConfig(level=logging.INFO,
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S',
                    filename=log_path + file_name,
                    filemode='a')
logger = logging.getLogger()
logger.addHandler(logging.StreamHandler())


# 数据库操作类

# mitmdump -s decode_douyin_data.py -p 8899
class Dbdata_handle():
    db_conn = False

    def __init__(self):
        return

    def getdb(self):
        try:
            if self.db_conn:
                self.db_conn.ping(reconnect=True)
                return self.db_conn
            else:
                self.db_conn = pymysql.connect(
                    host='127.0.0.1', user='douyin', passwd='douyin123456', db='douyin', port=3306, charset='utf8mb4')
                # pymysql.connect(host='127.0.0.1', user='douyin', passwd='douyin123456', db='douyin',charset='utf8')
                return self.db_conn
        except Exception as e:
            logging.exception(e)
            return False

    # 添加数据方法(table:表名,dict:一条数据,字典)
    def handle_data_add(self, table, data):
        try:
            db_conn = self.getdb()
            cursor = db_conn.cursor()
            sql3, listdata = self.dict_get_key_sql(data)
            # 插入一条数据sql
            sql = "insert ignore into {0}{1}".format(table, sql3)
            # logging.info(sql)

            bool = cursor.execute(sql, listdata)
            db_conn.commit()
            print(bool, '11111111')
            cursor.close()
        except Exception as e:
            logging.exception(e)
            logging.info('handle_data_add failed')
            logging.info(sql)

    # 添加、更新数据方法-存在更新，不存在添加(table:表名,dict:一条数据,字典)
    def handle_data_duplicate_add(self, table, dict):
        try:
            # 连接数据库
            db_conn = self.getdb()
            # 获取游标
            cursor = db_conn.cursor()
            sql3 = self.dict_add_sql(dict)
            # 存在更新，不存在添加sql
            sql = "insert into {0}{1} on DUPLICATE key update status = 2".format(
                table, sql3)
            # logging.info(sql)
            data = cursor.execute(sql)
            db_conn.commit()
            print(data, '222222222')
            cursor.close()
        except Exception as e:
            logging.exception(e)
            logging.info('handle_data_duplicate_add failed')

    # 更新数据方法(table:表名,field:字段,field_val:字段值,symbol:符号,dict:一条数据,字典)

    def handle_data_update(self, table, field, field_val, symbol, dict):
        try:
            # 连接数据库
            db_conn = self.getdb()
            # 获取游标
            cursor = db_conn.cursor()
            sql1 = self.dict_update_sql(dict)
            # 更新一条数据sql
            sql = "update {0} set {1} where {2} = {4}{3}{4}".format(
                table, sql1, field, field_val, symbol)
            # logging.info(sql)
            data = cursor.execute(sql)
            db_conn.commit()
            print(data, '333333333')
            cursor.close()
        except Exception as e:
            logging.exception(e)
            logging.info('handle_data_update failed')

    # 获取视频信息表里面的视频id(get_field:需要获取信息的字段, table:表明, field_one:条件字段1, filed_two:条件字段2)
    def get_video_info(self, get_field, table, field_one, filed_two):
        try:
            # 连接数据库
            db_conn = self.getdb()
            # 获取某个字段值sql
            cursor = db_conn.cursor()
            sql = "select {0} from {1} where {2} is null and {3} is null".format(
                get_field, table, field_one, filed_two)
            # logging.info(sql)
            cursor.execute(sql)
            db_conn.commit()
            res = cursor.fetchone()
            cursor.close()
            return res
        except Exception as e:
            logging.exception(e)
            logging.info('get_video_info failed')
            return False

    # 获取任意表里面的单个字段的集合(get_field:需要获取信息的字段, table:表明)

    def get_table_info(self, get_field, table):
        try:
            # 连接数据库
            db_conn = self.getdb()
            # 获取某个字段值sql
            cursor = db_conn.cursor()
            sql = "select {0} from {1}".format(get_field, table)
            # logging.info(sql)
            cursor.execute(sql)
            db_conn.commit()
            res = cursor.fetchall()
            cursor.close()
            return res
        except Exception as e:
            logging.exception(e)
            logging.info('get_video_info failed')
            return False

    # 获取用户uid,也就是分享id,然后取出使用(field:需要取出的字段,table:表名)
    def get_uid_info(self, field, table):
        try:
            # 连接数据库
            db_conn = self.getdb()
            cursor = db_conn.cursor()
            sql = "SELECT {0} FROM `{1}` where following_count is null and follower_count is null and total_favoritedhandle_data_duplicate_add is null".format(
                field, table)
            # logging.info(sql)
            cursor.execute(sql)
            db_conn.commit()
            res = cursor.fetchall()
            cursor.close()
            return res
        except Exception as e:
            logging.exception(e)
            logging.info('get_uid_info failed')

    # 根据字典结构拼装插入的sql语句
    def dict_add_sql(self, dict):
        sql = ''
        sql1 = ''
        sql2 = ''

        for key in dict:
            if dict[key] != '':
                if type(dict[key]) == str:
                    if dict[key] == 'null':
                        sql1 += "`{0}`,".format(key)
                        sql2 += "{0},".format(dict[key])
                    else:
                        sql1 += "`{0}`,".format(key)
                        sql2 += "'{0}',".format(dict[key])
                else:
                    sql1 += "`{0}`,".format(key)
                    sql2 += "{0},".format(dict[key])
        sql1 = sql1[:-1]
        sql2 = sql2[:-1]
        sql = '({0}) values ({1})'.format(sql1, sql2)
        return sql

    def dict_get_key_sql(self, data):
        sql0 = ''
        sql1 = ''
        sql2 = ''
        listdata = []
        for key in data:
            # if isinstance(data[key], dict):
            if type(data[key]) == str:
                sql1 += "`{0}`,".format(key)
                sql2 += "%s,"
                listdata.append(data[key])
            else:
                sql1 += "`{0}`,".format(key)
                sql2 += "%s,"
                listdata.append(json.dumps(data[key]))

        sql1 = sql1[:-1]
        sql2 = sql2[:-1]
        sql0 = '({0}) values ({1})'.format(sql1, sql2)
        return sql0, listdata

    # 根据字典结构拼装跟新的sql语句
    def dict_update_sql(self, dict):
        sql = ''
        for key in dict:
            if dict[key] != '':
                if type(dict[key]) == str:
                    sql += "`{0}`='{1}',".format(key, dict[key])
                else:
                    sql += "`{0}`={1},".format(key, dict[key])
        sql = sql[:-1]
        return sql

    # 字典组建
    def assoc_dict(self, tuple1, cursor_tag):
        try:
            data_list = []
            for field in cursor_tag.description:
                data_list.append(field[0])

            list_all = []
            is_type = isinstance(tuple1[0], tuple)
            if is_type:
                # 如果是二维元组
                for list1 in range(len(tuple1)):
                    tuple_data = tuple1[list1]
                    dict1 = {}
                    for x in range(len(data_list)):
                        dict1[data_list[x]] = tuple_data[x]
                    list_all.append(dict1)
            else:
                # 如果是一维元组
                dict1 = {}
                for x in range(len(data_list)):
                    dict1[data_list[x]] = tuple1[x]
                list_all.append(dict1)
        except Exception as e:
            pass
        return list_all

    def test(self):
        try:
            db_conn = self.getdb().xxx()
        except Exception as e:
            logging.exception(e)
            logging.info('test')


db_data = Dbdata_handle()
# while True:
#     db_data.handle_data_add("test",{"data":time.time()})
#     time.sleep(1)

# db_data = Dbdata_handle()
# while True:
#     try:
#         time.sleep(1)
#         db_data.test()
#     except Exception as e:
#         logging.exception(e)
#         print("xxx")